package com.jn.erp.web.api.student.utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.util.List;

/**
 * <pre>
 *
 * </pre>
 *
 * @author sunjiyong
 * @since 2024/4/2 9:25
 */
public class ExcelOutUtil {

    private HSSFSheet sheet;

    private HSSFWorkbook workbook;

    private int firstRow;

    private List<String> selectDateList;

    public ExcelOutUtil(HSSFWorkbook book, HSSFSheet sheet) {
        this.workbook = book;
        this.sheet = sheet;
    }


    public ExcelOutUtil createSelectDateList(List<String> selectDateList) {
        this.selectDateList = selectDateList;
        return this;
    }

    public ExcelOutUtil createFirstRow(int firstRow) {
        this.firstRow = firstRow;
        return this;
    }

    /**
     * 基于数据有效性序列设置下拉(字符数有限制)
     *
     * @param columnIndex 第几列从0开始
     */
    public ExcelOutUtil effectivenessSelectData(Integer columnIndex) {

        HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheet);
        DataValidationConstraint provConstraint = dvHelper.createExplicitListConstraint(selectDateList.toArray(new String[0]));
        setSelectParameter(sheet, dvHelper, provConstraint, columnIndex, columnIndex);
        return this;
    }

    //设置下拉
    public void setSelectParameter(HSSFSheet sheet, HSSFDataValidationHelper dvHelper, DataValidationConstraint provConstraint, int firstCol, int lastCol) {
        //四个参数分别是起始行、终止行、起始列、终止列
        CellRangeAddressList proRangeAddressList = new CellRangeAddressList(firstRow, 65535, firstCol, lastCol);
//        DataValidation provinceDataValidation = dvHelper.createValidation(provConstraint, proRangeAddressList);
//        //验证
//        provinceDataValidation.createErrorBox("error", "请选择正确的类型");
//        provinceDataValidation.setShowErrorBox(true);
//        provinceDataValidation.setSuppressDropDownArrow(true);
//        sheet.addValidationData(provinceDataValidation);
        // 创建下拉选择的选项
        DVConstraint constraint = DVConstraint.createExplicitListConstraint(selectDateList.toArray(new String[selectDateList.size()]));

        // 应用下拉选择
        HSSFDataValidation dataValidation = new HSSFDataValidation(proRangeAddressList, constraint);
        sheet.addValidationData(dataValidation);
    }



    //日期格式校验，输入日期为yyyy-MM-dd格式
    public ExcelOutUtil setDateFormatValidation(int columnIndex) {
        HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheet);

        // 设置整列的数据验证，确保日期大于等于1900-01-01
        DataValidationConstraint dateConstraint = dvHelper.createDateConstraint(
                DataValidationConstraint.OperatorType.GREATER_OR_EQUAL,
                "1900-01-01",
                null,
                "yyyy-MM-dd"
        );

        CellRangeAddressList dateRange = new CellRangeAddressList(firstRow, 1048575, columnIndex, columnIndex);
        DataValidation dateValidation = dvHelper.createValidation(dateConstraint, dateRange);

        // 设置错误消息
        dateValidation.createErrorBox("日期无效", "请输入格式为 yyyy-MM-dd 且大于等于 1900-01-01 的有效日期。");

        // 显示错误提示框并抑制下拉箭头
        dateValidation.setShowErrorBox(true);
        dateValidation.setSuppressDropDownArrow(true);

        // 将验证添加到工作表
        sheet.addValidationData(dateValidation);

        return this;
    }


    //日期格式校验，输入日期为yyyy-MM格式
    public ExcelOutUtil setDateFormatMonthValidation(int columnIndex) {
        HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheet);

        // 设置整列的数据验证，确保日期大于等于1900-01-01
        DataValidationConstraint dateConstraint = dvHelper.createDateConstraint(
                DataValidationConstraint.OperatorType.GREATER_OR_EQUAL,
                "1900-01",
                null,
                "yyyy-MM"
        );

        CellRangeAddressList dateRange = new CellRangeAddressList(firstRow, 1048575, columnIndex, columnIndex);
        DataValidation dateValidation = dvHelper.createValidation(dateConstraint, dateRange);

        // 设置错误消息
        dateValidation.createErrorBox("日期无效", "请输入格式为 yyyy-MM 且大于等于 1900-01 的有效日期。");

        // 显示错误提示框并抑制下拉箭头
        dateValidation.setShowErrorBox(true);
        dateValidation.setSuppressDropDownArrow(true);

        // 将验证添加到工作表
        sheet.addValidationData(dateValidation);

        return this;
    }

}
